﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DTO;
namespace DAO
{
    public class MonHoc_DAO
    {
        //load thong tin mon hoc len bang
        public static DataTable LoadMonHoc()
        {
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spLoadMonHoc", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //them thông tin môn học
        public static void ThemMonHoc(MonHoc_DTO mh)
        {
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spThemMonHoc", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaMH", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@TenMH", SqlDbType.NChar, 30);
            cmd.Parameters.Add("@SoTC", SqlDbType.Int);
            cmd.Parameters.Add("@LoaiMon", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@MoTa", SqlDbType.NText);
            //gán giá trị
            cmd.Parameters["@MaMH"].Value = mh.MaMH;
            cmd.Parameters["@TenMH"].Value = mh.TenMH;
            cmd.Parameters["@SoTC"].Value = mh.SoTC;
            cmd.Parameters["@LoaiMon"].Value = mh.LoaiMon;
            cmd.Parameters["@MoTa"].Value = mh.MoTa;
            //
            if (cnn.State == ConnectionState.Open)
                cnn.Close();
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //sua mmon hoc
        public static void SuaMH(MonHoc_DTO mh)
        {
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spSuaThongTinMH", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaMH", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@TenMH", SqlDbType.NChar, 30);
            cmd.Parameters.Add("@SoTC", SqlDbType.Int);
            cmd.Parameters.Add("@LoaiMon", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@MoTa", SqlDbType.NText);
            //gán giá trị
            cmd.Parameters["@MaMH"].Value = mh.MaMH;
            cmd.Parameters["@TenMH"].Value = mh.TenMH;
            cmd.Parameters["@SoTC"].Value = mh.SoTC;
            cmd.Parameters["@LoaiMon"].Value = mh.LoaiMon;
            cmd.Parameters["@MoTa"].Value = mh.MoTa;
            //
             if (cnn.State == ConnectionState.Open)
                cnn.Close();
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        public static void XoaMH(string mh)
        {

            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spXoaMH", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaMH", SqlDbType.NChar, 10);
            //
            cmd.Parameters["@MaMH"].Value = mh;
            //
            if (cnn.State == ConnectionState.Open)
            {
                cnn.Close();
            }
            cnn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch(SqlException)
            {
                throw new Exception("Lỗi kết nối dữ liệu!");
            }
            cnn.Close();
        }
        // kiem tra xem ma mon hoc da ton tai chua
        public static bool kiemTraMaMH(string matk)
        {
            int exist = 0;
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spKiemTraMaMH", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            //
            cmd.Parameters.Add("@MaMH", SqlDbType.NChar, 10);
            //
            cmd.Parameters["@MaMH"].Value = matk;
            //
            if (cnn.State == ConnectionState.Open)
                cnn.Close();
            cnn.Open();
            exist = (int)cmd.ExecuteScalar();
            cnn.Close();
            if (exist > 0)
                return true;
            return false;
        }
    }
}
